package com.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtil {
	private static DataSource datasource;
	static {
		datasource = new ComboPooledDataSource("mysql");
	}

	public static Connection getConn() {
		Connection con = null;
		try {
			con = datasource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	public static void close(Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(Connection con, Statement stm, ResultSet rs) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stm != null) {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static int update(String sql, Object... params) {// ���ܸ���
		int result = 0;
		Connection con = null;
		try {
			con = DBUtil.getConn();
			result = new QueryRunner().update(con, sql, params);

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return result;
	}

	// 查询单个对象
	public static <T> T getOneObject(String sql, Class<T> clazz, Object... params) {
		T t = null;
		Connection con = null;
		try {
			con = DBUtil.getConn();
			t = new QueryRunner().query(con, sql, new BeanHandler<T>(clazz), params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return t;
	}
	//查询一组对象(如果查到的数据为空,返回的是一个空列表,不是null)
	public static <T>  List<T> getList(String sql,Class<T> clazz,  Object ...  params){
		List<T> list=new ArrayList<T>();
		Connection conn=null;
		try {
			conn=getConn();
			list=new QueryRunner().query(conn,sql, new BeanListHandler<T>(clazz),params);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			close(conn);
		}
				
		return list;
	}

	// 添加用户返回自增id
	public static int getAddAndById(String sql, Object... params) {
		int result = 0;
		Connection con = null;
		PreparedStatement stm = null;
		ResultSet rs = null;
		try {
			con = DBUtil.getConn();
			stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < params.length; i++) {
				stm.setObject(i + 1, params[i]);
			}
			stm.execute();
			rs = stm.getGeneratedKeys();
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con, stm, rs);
			;
		}
		return result;
	}

	// 查询一组对象
	public static <T> List<T> getAllUser(String sql, Class<T> clazz, Object... params) {
		List<T> list = new ArrayList<>();
		Connection con = null;
		try {
			con = DBUtil.getConn();
			list = new QueryRunner().query(con, sql, new BeanListHandler<T>(clazz), params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return list;
	}

	// 查询一个对象返回一个map集合 key是字段名 value是字段
	public static Map<String, Object> getOneMap(String sql, Object... params) {
		Map<String, Object> map = new HashMap<>();
		Connection con = null;
		try {
			con = DBUtil.getConn();
			map = new QueryRunner().query(con, sql, new MapHandler(), params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return map;
	}

	// 返回一组对象 放入map集合 每条数据都是一个map集合
	public static List<Map<String, Object>> getaAllMap(String sql, Object... params) {
		List<Map<String, Object>> list = new ArrayList<>();
		Connection con = null;
		try {
			con = DBUtil.getConn();
			list = new QueryRunner().query(con, sql, new MapListHandler(), params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return list;
	}

	// 返回一个单个数据
	public static <T> T getScalar(String sql, Object... params) {
		T t = null;
		Connection con = null;
		try {
			con = DBUtil.getConn();
			t = new QueryRunner().query(con, sql, new ScalarHandler<T>(1), params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return t;
	}
	//返回一组数据
	public static  List<Object> getScalarList(String sql, Object... params ) {
		List<Object> list = new ArrayList<>();
		Connection con = null;
		try {
			con = DBUtil.getConn();
			List<Map<String, Object>> map = getaAllMap(sql, params);
			for(Map<String, Object> m:map) {
				m.forEach((K,V)->list.add(V));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(con);
		}
		return list;
	}
}
